** Alternative Capability Rankings (price)
** This do-file is necessary for conducting robustness check shown in Table A24 

/* input 
${output_data}matched_exp_imp_hs6_junedecember_maquilaprice_restrict.dta
${output_data}quota_mex_weight_hs6.dta
*/

/* output
${output_data}mexico_for_analysis_04_07_hs6_restrict_price.dta
${output_data}US_for_analysis_04_07_hs6_restrict_price.dta
*/

*************************
*Step 1 Create Mexican downgrading 6-digit level data
*************************

*** Create Mexican downgrading 6-digit level using price ranking with MAIN PARTNER 
use "${output_data}matched_exp_imp_hs6_junedecember_maquilaprice_restrict.dta", clear
keep rfc hs6 year cveunidad canto newclid2 value_exporter_importer
keep if year==2004
drop if newclid2==.
label var newclid2 "importer id"
label var value_exporter_importer "exports of hs6 between exporter and importer"
label var rfc "exporter id"
label var hs6 "hs6"

collapse (sum) canto value_exporter_importer, by(rfc newclid2 hs6 cve)
gen price=value_exporter_importer/canto
gen exporter_id=rfc
tostring newclid2, generate(importer_id)
tostring cve, replace
gen hs6_cve_importer_id=hs6+"_"+cve+"_"+ importer_id
gen hs6_cve_exporter_id=hs6+"_"+cve+"_"+ rfc
gen hs2=substr(hs6,1,2)
drop if (hs2=="50" | hs2=="51" | hs2=="52" | hs2=="53" | hs2=="54" | hs2=="55" | hs2=="60" ) & (cve=="6" | cve=="7" | cve=="11" | cve=="12" | cve=="17" | cve=="18" | cve=="19" | cve=="20")
save "${temp_data_price}data_for_ranking_hs6_price_yearly.dta", replace

use "${temp_data_price}data_for_ranking_hs6_price_yearly.dta", clear
gsort hs6_cve_importer_id -value_exporter_importer
by hs6_cve_importer_id: gen exporter_rank=_n
keep if exporter_rank==1
drop exporter_rank
rename exporter_id main_exporter
gsort hs6 cve -price newclid2
by hs6 cve: gen p_rank=_n
bys hs6 cve: egen hs6_cve_price=mean(price)
tostring p_rank, generate(p_rank_s)
gen hs6_cve_importer_id_p_rank=hs6_cve_importer_id+"_"+p_rank_s
drop p_rank_s
gen p_partner=hs6_cve_exporter_id 
label var p_partner "id of exporter with whom importer has bigger price match"
label var p_rank "ranking of match prices for importer"
save "${temp_data_price}data_for_ranking_hs6_p_rank.dta", replace

use "${output_data}matched_exp_imp_hs6_junedecember_maquilaprice_restrict.dta", clear
drop if newclid2==.
keep rfc hs6 year cveunidad canto newclid2 value_exporter_importer
collapse (sum) canto value_exporter_importer, by(rfc newclid2 hs6 cve year)
gen price=value_exporter_importer/canto
tostring newclid2, generate(importer_id)
tostring cve, replace
gen hs6_cve_importer_id=hs6+"_"+cve+"_"+ importer_id
gen hs6_cve_exporter_id=hs6+"_"+cve+"_"+ rfc
gen hs2=substr(hs6,1,2)
drop if (hs2=="50" | hs2=="51" | hs2=="52" | hs2=="53" | hs2=="54" | hs2=="55" | hs2=="60" ) & (cve=="6" | cve=="7" | cve=="11" | cve=="12" | cve=="17" | cve=="18" | cve=="19" | cve=="20")
save "${temp_data_price}exporter_importer_hs6.dta", replace

* Prepare for making data on downgrading
use "${temp_data_price}exporter_importer_hs6.dta", replace
tostring year, generate(year_s)
gen hs6_cve_exporter_id_year= hs6_cve_exporter_id+"_"+year_s
gsort hs6_cve_exporter_id_year -value_exporter_importer
by hs6_cve_exporter_id_year: gen importer_rank=_n
save "${temp_data_price}prep_downgrading_1.dta", replace

* Obtain number of exporters in 2004 by product-unit
use "${temp_data_price}prep_downgrading_1.dta", clear
keep if year==2004
keep hs6_cve_exporter_id hs6 cve
duplicates drop
gen n_exporters_2004=1
collapse (sum) n_exporters_2004, by(hs6 cve)
label var n_exporters_2004 "number of exporters in 2004 by product-unit"
save "${temp_data_price}n_exporters_2004.dta", replace

* Obtain number of importers in 2004 by product-unit
use "${temp_data_price}prep_downgrading_1.dta", replace
keep if year==2004
keep hs6_cve_importer_id hs6 cve
duplicates drop
gen n_importers_2004=1
collapse (sum) n_importers_2004, by(hs6 cve)
label var n_importers_2004 "number of importers in 2004 by product-unit"
save "${temp_data_price}n_importers_2004.dta", replace

* Merge with data on number of importers/exporters in 2004 by product-unit
use "${temp_data_price}prep_downgrading_1.dta", replace
merge m:1 hs6 cve using "${temp_data_price}n_exporters_2004.dta"
drop _merge
merge m:1 hs6 cve using "${temp_data_price}n_importers_2004.dta"
drop _merge
save "${temp_data_price}downgrading.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007 {
use "${temp_data_price}downgrading.dta", replace
keep if importer_rank==1 
keep if year==`j'
keep hs6_cve_importer_id hs6_cve_exporter_id
merge m:1 hs6_cve_importer_id using "${temp_data_price}data_for_ranking_hs6_p_rank.dta"
drop if _merge!=3
rename hs6_cve_importer_id main`j'_id
rename p_rank p_rank`j'
keep hs6_cve_exporter_id main`j'_id p_rank`j'
save "${temp_data_price}main`j'.dta", replace
}

* Create data on 2004-2007 for Mexico 
use "${temp_data_price}downgrading.dta", clear 
keep if year==2004
keep hs6 cve rfc hs6_cve_exporter_id n_*
duplicates drop
merge m:1 hs6_cve_exporter_id using "${temp_data_price}main2004.dta"
drop if _merge==2
drop _merge
merge m:1 hs6_cve_exporter_id using "${temp_data_price}main2007.dta"
drop if _merge==2
drop _merge
* Identify whether the ranking was downgrading
gen p_rank_d0704= p_rank2007-p_rank2004
gen i_p_rank_d0704=1 if p_rank_d0704>0
replace  i_p_rank_d0704=0 if p_rank_d0704<=0
replace  i_p_rank_d0704=. if p_rank_d0704==.
label var p_rank_d0704 "p_rank_2007-p_rank_2004"
label var i_p_rank_d0704 "1 if p_rank_d0704>0 (downgrade) and 0 otherwise"
* Create data for analysis (Mexico)
keep hs6 cve main2004_id hs6_cve_exporter_id p_rank_d0704 i_p_rank_d0704 p_rank2004 p_rank2007 n_importers_2004
gen newclid2=substr(main2004_id,12,length(main2004_id))
replace newclid2=subinstr(newclid2,"_","",.)
destring newclid2, replace
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge
gen hs2=substr(hs6,1,2)

save "${output_data}mexico_for_analysis_04_07_hs6_price_restrict.dta", replace


*************************
*Step 2 Create US upgrading 6-digit level data
*************************

*** Create US upgrading 6-digit level using price ranking with MAIN PARTNER
use "${temp_data_price}data_for_ranking_hs6_price_yearly.dta", clear
gsort hs6_cve_exporter_id -value_exporter_importer
by hs6_cve_exporter_id: gen importer_rank=_n
keep if importer_rank==1
drop importer_rank
rename importer_id main_importer

gsort hs6 cve -price rfc
by hs6 cve: gen p_rank=_n
bys hs6 cve: egen hs6_cve_price=mean(price)
tostring p_rank, generate(p_rank_s)
gen hs6_cve_exporter_id_p_rank=hs6_cve_exporter_id+"_"+p_rank_s
drop p_rank_s
gen p_partner=hs6_cve_importer_id 
label var p_partner "id of importer with whom exporter has bigger price match"
label var p_rank "ranking of match prices for exporter"
save "${temp_data_price}data_for_exp_ranking_hs6_p_rank.dta", replace

* Prepare for making data on upgrading
use "${temp_data_price}exporter_importer_hs6.dta", clear
tostring year, generate(year_s)
gen hs6_cve_importer_id_year= hs6_cve_importer_id+"_"+year_s
gsort hs6_cve_importer_id_year -value_exporter_importer
by hs6_cve_importer_id_year: gen exporter_rank=_n
save "${temp_data_price}prep_upgrading_1.dta", replace

* Obtain number of exporters in 2004 by product-unit
use "${temp_data_price}prep_upgrading_1.dta", clear
keep if year==2004
keep hs6_cve_exporter_id hs6 cve
duplicates drop
gen n_exporters_2004=1
collapse (sum) n_exporters_2004, by(hs6 cve)
label var n_exporters_2004 "number of exporters in 2004 by product-unit"
save "${temp_data_price}num_exporters_2004.dta", replace

* Obtain number of importers in 2004 by product-unit
use "${temp_data_price}prep_upgrading_1.dta", clear
keep if year==2004
keep hs6_cve_importer_id hs6 cve
duplicates drop
gen n_importers_2004=1
collapse (sum) n_importers_2004, by(hs6 cve)
label var n_importers_2004 "number of importers in 2004 by product-unit"
save "${temp_data_price}num_importers_2004.dta", replace

* Merge with data on number of importers/exporters in 2004 by product-unit
use "${temp_data_price}prep_upgrading_1.dta", clear
merge m:1 hs6 cve using "${temp_data_price}num_exporters_2004.dta"
drop _merge
merge m:1 hs6 cve using "${temp_data_price}num_importers_2004.dta"
drop _merge
save "${temp_data_price}upgrading.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007 {
use "${temp_data_price}upgrading.dta", clear
keep if exporter_rank==1 
keep if year==`j'
keep hs6_cve_importer_id hs6_cve_exporter_id
merge m:1 hs6_cve_exporter_id using "${temp_data_price}data_for_exp_ranking_hs6_p_rank.dta"
drop if _merge!=3
rename hs6_cve_exporter_id main`j'_id
rename p_rank p_rank`j'
keep hs6_cve_importer_id main`j'_id p_rank`j'
save "${temp_data_price}main`j'_US.dta", replace
}

* Create data on 2004-2007 for US
use "${temp_data_price}upgrading.dta", clear 
keep if year==2004
keep hs6 cve newclid2 hs6_cve_importer_id n_*
duplicates drop
merge m:1 hs6_cve_importer_id using "${temp_data_price}main2004_US.dta"
drop if _merge==2
drop _merge
merge m:1 hs6_cve_importer_id using "${temp_data_price}main2007_US.dta"
drop if _merge==2
drop _merge
* Identify whether the ranking was upgrading
gen e_p_rank_d0704= p_rank2007-p_rank2004
gen e_i_p_rank_d0704=1 if e_p_rank_d0704<0
replace  e_i_p_rank_d0704=0 if e_p_rank_d0704>=0
replace  e_i_p_rank_d0704=. if e_p_rank_d0704==.
label var e_p_rank_d0704 "p_rank_2007-p_rank_2004"
label var e_i_p_rank_d0704 "1 if p_rank_d0704<0 (upgrade) and 0 otherwise"
* Create data for analysis (US)
keep hs6 cve main2004_id hs6_cve_importer_id newclid2 e_p_rank_d0704 e_i_p_rank_d0704 p_rank2004 p_rank2007 n_exporters_2004
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge

gen hs2=substr(hs6,1,2)

save "${output_data}US_for_analysis_04_07_hs6_price_restrict.dta", replace
